package dao;

import model.Goods;
import model.Type;
import model.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import utils.DataSourceUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class GoodsDao {

    public void insert(Goods g) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "insert into goods(name,cover,price,intro,stock,type_id) values(?,?,?,?,?,?)";
        r.update(sql, g.getName(), g.getCover(), g.getPrice(), g.getIntro(), g.getStock(), g.getType().getId());
    }

    public Goods getGoodsById(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select g.id,g.name,g.cover,g.price,g.intro,g.stock,t.id typeid,t.name typename from goods g, type t where g.id =? and g.type_id=t.id";
        return r.query(sql, new BeanHandler<Goods>(Goods.class), id);

    }

    public void delete(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "delete from goods where id=?";
        r.update(sql, id);
    }

    public List<Goods> getGoodsList() {
        try {
            QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
            String sql = "SELECT g.*, t.name as typename FROM goods g LEFT JOIN type t ON g.type_id = t.id";
            List<Goods> list = r.query(sql, new BeanListHandler<Goods>(Goods.class));
            for (Goods g : list) {
                if (g.getType() == null) {
                    Type type = new Type();
                    type.setName("默认分类");
                    g.setType(type);
                }
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    public Goods getUserById(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select * from goods where id=?";
        return r.query(sql, new BeanHandler<Goods>(Goods.class), id);
    }

    public void updateGoodsAddress(Goods goods) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "update goods set name=?,cover=?,price=?,intro=? ,stock=? ,type_id=? where id=?";
        r.update(sql, goods.getName(), goods.getCover(), goods.getPrice(), goods.getIntro(), goods.getStock(), goods.getType().getId(), goods.getId());
    }

}
